USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ClassesSelectTop25Stats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ClassesSelectTop25Stats
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Classes Table Top 25 Stats Select
-- =============================================
CREATE PROCEDURE ClassesSelectTop25Stats

	@ClassId int

AS

BEGIN


	DECLARE @PlayerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int,
		@HeadShotPerctage float


DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	HeadShotPercentage float,
	PlayerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR

SELECT Top 25 COUNT(KillId) [Kills], Attacker_playerId
FROM Kills
WHERE Attacker_classId = @ClassId
GROUP BY Attacker_playerId
ORDER BY [Kills] Desc

OPEN c1

FETCH NEXT FROM c1
INTO @Kills, @PlayerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerId 
	GROUP BY PlayerName

	
	SELECT @Deaths = COUNT(KillId) FROM Kills WHERE Victim_playerId = @PlayerId
						AND Attacker_classId = @ClassId

	INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, 0.0, 0.0, @PlayerId)

	FETCH NEXT FROM c1
	INTO @Kills, @PlayerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, HeadShotPercentage, PlayerId FROM @tempPlayers
ORDER BY Kills DESC, KillDeathRatio DESC, Deaths DESC
	
END
GO


-- ClassesSelectTop25Stats 2